package com.xm.dao.reception_desk;

import com.xm.entity.*;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 订单Dao层
 *
 * @author 康文程
 * @date 2024-1-8
 */
public class OrdersDao extends BaseUtil {
    /**
     * 新增订单
     *
     * @param orders 订单
     * @throws SqlErrorException 抛出错误
     */
    public void insertOrder(Orders orders) throws SqlErrorException {
        @Language("sql")
        String sql = "INSERT INTO orders (orders_id, " +
                "payment_price, " +
                "coupon_credit, " +
                "total_merchandise, " +
                "estimated_time_arrival, " +
                "order_expiration_time, " +
                "commodity_ids, " +
                "address_id, " +
                "specification_ids, " +
                "insurance_ids, " +
                "warehouse_id, " +
                "coupons_id, " +
                "freight," +
                "number_products) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        executeUpdate(sql,
                orders.getOrdersId(),
                orders.getPaymentPrice(),
                orders.getCouponCredit(),
                orders.getTotalMerchandise(),
                simpleDateFormat.format(orders.getEstimatedTimeArrival()),
                simpleDateFormat.format(orders.getOrderExpirationTime()),
                orders.getCommodityIds(),
                orders.getAddressId(),
                orders.getSpecificationIds(),
                orders.getInsuranceIds(),
                orders.getWarehouseId(),
                orders.getCouponsId(),
                orders.getFreight(),
                orders.getNumberProducts());
    }

    /**
     * 根据订单ID查询订单到期时间
     *
     * @param orderId 订单ID
     * @return 订单到期剩余时间（分钟）
     * @throws SqlErrorException SQL执行异常
     */
    public int getOrderExpirationTimeByOrderId(String orderId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT order_expiration_time FROM orders WHERE orders_id = ?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, orderId);
            if (resultSet.next()) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date orderExpirationTime = sdf.parse(resultSet.getString("order_expiration_time"));

                long currentTime = System.currentTimeMillis();
                long expirationTime = orderExpirationTime.getTime();
                long remainingTime = expirationTime - currentTime;
                return (int) (remainingTime / (1000 * 60));
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
        return 0;
    }

    public int getOrderExpirationTime(Integer informationId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT o.order_expiration_time,o.place_order_date FROM orders o" +
                " LEFT JOIN address a ON o.address_id = a.address_id" +
                " WHERE a.information_id = ?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql,informationId);
            if (resultSet.next()) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date orderExpirationTime = sdf.parse(resultSet.getString("order_expiration_time"));
                Date PlaceOrderDate = sdf.parse(resultSet.getString("place_order_date"));

                long expirationTime = orderExpirationTime.getTime();
                long PlaceDate = PlaceOrderDate.getTime();
                long remainingTime = expirationTime - PlaceDate;
                return (int) (remainingTime / (1000 * 60));
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
        return 0;
    }

    /**
     * 根据订单ID修改订单状态
     *
     * @param orderId 订单ID
     * @param status  订单状态
     * @throws SqlErrorException SQL执行异常
     */
    public void updateOrderStatusByOrderId(String orderId, int status) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE orders SET orders_status = ? WHERE orders_id = ?";
        executeUpdate(sql, status, orderId);
    }

    /**
     * 根据订单ID查询订单金额
     *
     * @param orderId 订单ID
     * @return 订单金额
     * @throws SqlErrorException SQL执行异常
     */
    public BigDecimal getPaymentPriceByOrderId(String orderId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT payment_price FROM orders WHERE orders_id = ?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, orderId);
            if (resultSet.next()) {
                return resultSet.getBigDecimal("payment_price");
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
        return new BigDecimal(0);
    }

    /**
     * 根据用户信息ID查询订单列表
     * 根据订单状态查询
     *
     * @param informationId 用户信息ID
     * @return 订单列表
     * @throws SqlErrorException SQL执行异常
     */
    public List<Orders> getOrdersByInformationId(Integer informationId, String status, String searchInput) throws SQLException, ParseException {
        @Language("sql")
        String sql = "SELECT o.orders_id, " + "a.address_name,a.address_id,a.address_phone,a.address_city,a.address_district,a.address_labels,a.address_province,a.full_address," + "o.payment_price, " +
                "o.coupon_credit, " + "o.total_merchandise, " + "o.estimated_time_arrival, " +
                "o.mode_of_delivery, " + "o.orders_status, " + "o.place_order_date, " +
                "o.order_expiration_time, " + "o.payment_methods, " + "o.commodity_ids, " +
                "o.logistics_station_id, " + "o.specification_ids, " + "o.insurance_ids, " +
                "o.freight, " + "o.number_products, " + "c.coupon_title, " + "lc.logistics_companies_name, " +
                "lc.logistics_companies_phone, " + "ls.logistics_station_name, " + "ls.logistics_station_phone, " +
                "w.warehouse_name,w.warehouse_longitude,w.warehouse_latitude,"+
                "lo.logistics_station_city,lo.logistics_station_deliveryman,lo.logistics_station_district,lo.logistics_station_latitude," +
                "lo.logistics_station_longitude,lo.logistics_station_name,lo.logistics_station_phone,lo.logistics_station_province "+
                "FROM orders o " + "LEFT JOIN address a ON o.address_id = a.address_id " +
                "LEFT JOIN account_information ai ON a.information_id = ai.information_id " +
                "LEFT JOIN coupons c ON o.coupons_id = c.coupon_id " +
                "LEFT JOIN logistics_companies lc ON o.logistics_companies_id = lc.logistics_companies_id " +
                "LEFT JOIN logistics_station ls ON o.logistics_station_id = ls.logistics_station_id " +
                "LEFT JOIN commodity co ON co.commodity_id = o.commodity_ids " +
                "LEFT JOIN warehouse w ON w.warehouse_id = o.warehouse_id " +
                "LEFT JOIN logistics_station lo ON lo.logistics_companies_id = o.logistics_companies_id " +
                "WHERE ai.information_id = ?";
        //订单状态范围
        String isNotNull = "";
        if (status != null && !status.equals(isNotNull)) {
            String state = "4678";
            if (status.equals(state)) {
                sql += " and 3<o.orders_status<9";
            }else{
                sql += " and o.orders_status=" + status;
            }
        }
        //商品名称、订单号搜索
        if (searchInput != null) {
            String xm = "xm";
            boolean ret = searchInput.contains(xm);
            if (ret) {
                sql += " and o.orders_id like '%" + searchInput + "%'";
            } else {
                sql += " and co.commodity_name like '%" + searchInput + "%'";
            }
        }
        sql+="";


        List<Orders> ordersList = new ArrayList<>();
        ResultSet insuranceResultSet = null;
        ResultSet separateResultSet = null;
        ResultSet resultSet = executeQuery(sql, informationId);
        while (resultSet.next()) {
            Orders orders = new Orders();
            orders.setOrdersId(resultSet.getString("orders_id"));
            orders.setAddressName(resultSet.getString("address_name"));
            orders.setPaymentPrice(resultSet.getBigDecimal("payment_price"));
            orders.setCouponCredit(resultSet.getBigDecimal("coupon_credit"));
            orders.setTotalMerchandise(resultSet.getInt("total_merchandise"));
            orders.setEstimatedTimeArrival(resultSet.getTimestamp("estimated_time_arrival"));
            orders.setModeOfDelivery(resultSet.getInt("mode_of_delivery"));
            orders.setOrdersStatus(resultSet.getInt("orders_status"));
            orders.setPlaceOrderDate(resultSet.getTimestamp("place_order_date"));
            orders.setOrderExpirationTime(resultSet.getTimestamp("order_expiration_time"));
            orders.setPaymentMethods(resultSet.getInt("payment_methods"));

            String separate = ",";

            // 将commodity_ids字段拆分为ID数组，构建链表查询条件
            if (resultSet.getString("commodity_ids") != null) {
                String[] ids = resultSet.getString("commodity_ids").split(separate);
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < ids.length; i++) {
                    if (i > 0) {
                        sb.append(" OR ");
                    }
                    sb.append("c.commodity_id = ").append(ids[i]);
                }
                @Language("sql")
                String commoditySql = "SELECT * FROM commodity c " +
                        "left join commodity_gifts cg on cg.commodity_gifts_id = c.commodity_gifts " +
                        "WHERE " + sb;
                ResultSet commodityResultSet = executeQuery(commoditySql);
                List<Commodity> commodityList = new ArrayList<>();
                int i = 0;
                while (commodityResultSet.next()) {
                    Commodity commodity = new Commodity();
                    commodity.setCommodityId(commodityResultSet.getInt("commodity_id"));
                    commodity.setCommodityName(commodityResultSet.getString("commodity_name"));
                    commodity.setCommodityImages(commodityResultSet.getString("commodity_images")
                            .split(separate)[0]);
                    commodity.setCommodityPrice(commodityResultSet.getBigDecimal("commodity_price"));

                    //存在多个商品就存在多个商品多个规格
                    String specificationIds = resultSet.getString("specification_ids").split("[|]")[i];
                    List<CommoditySpecification> specificationList = new ArrayList<>();
                    for (String idStr : specificationIds.split(separate)) {
                        if (idStr != null) {
                            StringBuilder specificationStringBuilder = new StringBuilder();
                            for (int x = 0; x < specificationIds.split(separate).length; x++) {
                                if (x > 0) {
                                    specificationStringBuilder.append(" OR ");
                                }
                                System.out.println("--------------");
                                System.out.println(specificationIds.split(separate)[x]);
                                specificationStringBuilder.append("specification_id = ")
                                        .append(specificationIds.split(separate)[x]);
                                System.out.println(specificationStringBuilder);

                            }
                            @Language("sql")
                            String separateSql = "select * from commodity_specification where " + specificationStringBuilder;
                            CommoditySpecification commoditySpecification = new CommoditySpecification();
                            separateResultSet = executeQuery(separateSql);
                            while (separateResultSet.next()) {
                                commoditySpecification.setSpecificationName(separateResultSet.getString("specification_name"));
                                commoditySpecification.setAddPrice(separateResultSet.getBigDecimal("add_price"));
                                specificationList.add(commoditySpecification);
                            }
                        }
                        commodity.setCommoditySpecification(specificationList);
                    }

                    if (resultSet.getString("insurance_ids") != null
                            && !resultSet.getString("insurance_ids").isEmpty()){
                        String insuranceIds = resultSet.getString("insurance_ids").split("[|]")[i];
                        List<CommodityInsurance> insuranceList = new ArrayList<>();
                        for (String idStr : insuranceIds.split(separate)) {
                            if (idStr != null) {
                                StringBuilder insuranceStringBuilder = new StringBuilder();
                                for (int z = 0; z < insuranceIds.split(separate).length; z++) {
                                    if (z > 0) {
                                        insuranceStringBuilder.append(" OR ");
                                    }
                                    insuranceStringBuilder.append("insurance_id = ")
                                            .append(insuranceIds.split(separate)[z]);
                                }
                                @Language("SQL")
                                String insuranceSql = "select * from commodity_insurance where " + insuranceStringBuilder;
                                insuranceResultSet = executeQuery(insuranceSql);
                                CommodityInsurance commodityInsurance = new CommodityInsurance();
                                while (insuranceResultSet.next()) {
                                    commodityInsurance.setInsuranceTitle(insuranceResultSet.getString("insurance_title"));
                                    commodityInsurance.setAddPrice(insuranceResultSet.getBigDecimal("add_price"));
                                    insuranceList.add(commodityInsurance);
                                }
                            }
                            commodity.setCommodityInsurances(insuranceList);
                            commodityList.add(commodity);
                        }
                    }
                    i++;
                    orders.setCommodityList(commodityList);
                }
            }


            orders.setLogisticsStationId(resultSet.getInt("logistics_station_id"));
            orders.setSpecificationIds(resultSet.getString("specification_ids"));
            orders.setInsuranceIds(resultSet.getString("insurance_ids"));
            orders.setFreight(resultSet.getBigDecimal("freight"));
            orders.setNumberProducts(resultSet.getString("number_products"));

            LogisticsCompanies logisticsCompanies = new LogisticsCompanies();
            logisticsCompanies.setLogisticsCompaniesName("logistics_companies_name");
            logisticsCompanies.setLogisticsCompaniesPhone("logistics_companies_phone");

            LogisticsStation logisticsStation = new LogisticsStation();
            logisticsStation.setLogisticsStationName("logistics_station_name");
            logisticsStation.setLogisticsStationPhone("logistics_station_phone");

            Coupons coupons = new Coupons();
            coupons.setCouponTitle("coupon_title");

            Warehouse warehouse=new Warehouse();
            warehouse.setWarehouseName(resultSet.getString("warehouse_name"));
            warehouse.setWarehouseLongitude(resultSet.getString("warehouse_longitude"));
            warehouse.setWarehouseLatitude(resultSet.getString("warehouse_latitude"));
            orders.setWarehouse(warehouse);

            ordersList.add(orders);

        }
        close(separateResultSet);
        close(insuranceResultSet);
        close(resultSet);
        return ordersList;
    }

    public List<Orders> getOrdersDetails(Integer informationId, String ordersId) throws SQLException {
        @Language("sql")
        String sql = "SELECT o.orders_id, " + "a.address_name,a.address_id,a.address_phone,a.address_city,a.address_district,a.address_labels,a.longitude,a.latitude,a.address_province,a.full_address," + "o.payment_price, " +
                "o.coupon_credit, " + "o.total_merchandise, " + "o.estimated_time_arrival, " +
                "o.mode_of_delivery, " + "o.orders_status, " + "o.place_order_date, " +
                "o.order_expiration_time, " + "o.payment_methods, " + "o.commodity_ids, " +
                "o.logistics_station_id, " + "o.specification_ids, " + "o.insurance_ids, " +
                "o.freight, " + "o.number_products, " + "c.coupon_title, " + "lc.logistics_companies_name, " +
                "lc.logistics_companies_phone, " + "ls.logistics_station_name, " + "ls.logistics_station_phone, " +
                "w.warehouse_name,w.warehouse_longitude,w.warehouse_latitude,"+
                "lo.logistics_station_city,lo.logistics_station_deliveryman,lo.logistics_station_district,lo.logistics_station_latitude," +
                "lo.logistics_station_longitude,lo.logistics_station_name,lo.logistics_station_phone,lo.logistics_station_province "+
                "FROM orders o " + "LEFT JOIN address a ON o.address_id = a.address_id " +
                "LEFT JOIN account_information ai ON a.information_id = ai.information_id " +
                "LEFT JOIN coupons c ON o.coupons_id = c.coupon_id " +
                "LEFT JOIN logistics_companies lc ON o.logistics_companies_id = lc.logistics_companies_id " +
                "LEFT JOIN logistics_station ls ON o.logistics_station_id = ls.logistics_station_id " +
                "LEFT JOIN commodity co ON co.commodity_id = o.commodity_ids " +
                "LEFT JOIN warehouse w ON w.warehouse_id = o.warehouse_id " +
                "LEFT JOIN logistics_station lo ON lo.logistics_companies_id = o.logistics_companies_id " +
                "WHERE ai.information_id = ? and orders_id like '%" + ordersId + "%'";

        Address address = new Address();
        List<Orders> ordersList = new ArrayList<>();
        ResultSet insuranceResultSet = null;
        ResultSet specificationResultSet = null;
        ResultSet resultSet = executeQuery(sql, informationId);
        while (resultSet.next()) {
            Orders orders = new Orders();
            orders.setOrdersId(resultSet.getString("orders_id"));
            orders.setAddressName(resultSet.getString("address_name"));
            orders.setPaymentPrice(resultSet.getBigDecimal("payment_price"));
            orders.setCouponCredit(resultSet.getBigDecimal("coupon_credit"));
            orders.setTotalMerchandise(resultSet.getInt("total_merchandise"));
            orders.setEstimatedTimeArrival(resultSet.getTimestamp("estimated_time_arrival"));
            orders.setModeOfDelivery(resultSet.getInt("mode_of_delivery"));
            orders.setOrdersStatus(resultSet.getInt("orders_status"));
            orders.setPlaceOrderDate(resultSet.getTimestamp("place_order_date"));
            orders.setOrderExpirationTime(resultSet.getTimestamp("order_expiration_time"));
            orders.setPaymentMethods(resultSet.getInt("payment_methods"));


            address.setAddressId(resultSet.getInt("address_id"));
            address.setAddressName(resultSet.getString("address_name"));
            address.setAddressPhone(resultSet.getString("address_phone"));
            address.setAddressProvince(resultSet.getString("address_province"));
            address.setAddressCity(resultSet.getString("address_city"));
            address.setAddressDistrict(resultSet.getString("address_district"));
            address.setFullAddress(resultSet.getString("full_address"));
            address.setAddressLabels(resultSet.getString("address_labels"));
            address.setLongitude(resultSet.getString("longitude"));
            address.setLatitude(resultSet.getString("latitude"));


            String separate = ",";

            // 将commodity_ids字段拆分为ID数组，构建链表查询条件
            if (resultSet.getString("commodity_ids") != null) {
                String[] ids = resultSet.getString("commodity_ids").split(separate);
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < ids.length; i++) {
                    if (i > 0) {
                        sb.append(" OR ");
                    }
                    sb.append("c.commodity_id = ").append(ids[i]);
                }
                @Language("sql")
                String commoditySql = "SELECT * FROM commodity c " +
                        "left join commodity_gifts cg on cg.commodity_gifts_id = c.commodity_gifts " +
                        "WHERE " + sb;
                ResultSet commodityResultSet = executeQuery(commoditySql);
                List<Commodity> commodityList = new ArrayList<>();
                int i = 0;
                while (commodityResultSet.next()) {
                    Commodity commodity = new Commodity();
                    commodity.setCommodityId(commodityResultSet.getInt("commodity_id"));
                    commodity.setCommodityName(commodityResultSet.getString("commodity_name"));
                    commodity.setCommodityImages(commodityResultSet.getString("commodity_images")
                            .split(separate)[0]);
                    commodity.setCommodityPrice(commodityResultSet.getBigDecimal("commodity_price"));
                    commodity.setCommodityDiscount(commodityResultSet.getInt("commodity_discount"));

                    //存在多个商品就存在多个商品多个规格
                    String specificationIds = resultSet.getString("specification_ids").split("[|]")[i];
                    List<CommoditySpecification> specificationList = new ArrayList<>();
                    for (String idStr : specificationIds.split(separate)) {
                        if (idStr != null) {
                            StringBuilder specificationStringBuilder = new StringBuilder();
                            for (int x = 0; x < specificationIds.split(separate).length; x++) {
                                if (x > 0) {
                                    specificationStringBuilder.append(" OR ");
                                }
                                specificationStringBuilder.append("specification_id = ")
                                        .append(specificationIds.split(separate)[x]);
                            }
                            @Language("sql")
                            String insuranceSql = "select * from commodity_specification where " + specificationStringBuilder;
                            CommoditySpecification commoditySpecification = new CommoditySpecification();
                            specificationResultSet = executeQuery(insuranceSql);
                            while (specificationResultSet.next()) {
                                commoditySpecification.setSpecificationName(specificationResultSet.getString("specification_name"));
                                commoditySpecification.setAddPrice(specificationResultSet.getBigDecimal("add_price"));
                                specificationList.add(commoditySpecification);
                            }
                        }
                        commodity.setCommoditySpecification(specificationList);
                    }

                    if (resultSet.getString("insurance_ids") != null) {
                        String insuranceIds = resultSet.getString("insurance_ids").split("[|]")[i];
                        List<CommodityInsurance> insuranceList = new ArrayList<>();
                        for (String idStr : insuranceIds.split(separate)) {
                            if (idStr != null) {
                                StringBuilder insuranceStringBuilder = new StringBuilder();
                                for (int z = 0; z < insuranceIds.split(separate).length; z++) {
                                    if (z > 0) {
                                        insuranceStringBuilder.append(" OR ");
                                    }
                                    insuranceStringBuilder.append("insurance_id = ")
                                            .append(insuranceIds.split(separate)[z]);
                                }
                                String insuranceSql = "select * from commodity_insurance where " + insuranceStringBuilder;
                                insuranceResultSet = executeQuery(insuranceSql);
                                CommodityInsurance commodityInsurance = new CommodityInsurance();
                                while (insuranceResultSet.next()) {
                                    commodityInsurance.setInsuranceTitle(insuranceResultSet.getString("insurance_title"));
                                    commodityInsurance.setAddPrice(insuranceResultSet.getBigDecimal("add_price"));
                                    insuranceList.add(commodityInsurance);
                                }
                            }
                            commodity.setCommodityInsurances(insuranceList);
                            commodityList.add(commodity);
                        }
                    }
                    i++;
                    orders.setCommodityList(commodityList);
                }
            }

            orders.setLogisticsStationId(resultSet.getInt("logistics_station_id"));
            orders.setSpecificationIds(resultSet.getString("specification_ids"));
            orders.setInsuranceIds(resultSet.getString("insurance_ids"));
            orders.setFreight(resultSet.getBigDecimal("freight"));
            orders.setNumberProducts(resultSet.getString("number_products"));
            orders.setAddress(address);

            LogisticsCompanies logisticsCompanies = new LogisticsCompanies();
            logisticsCompanies.setLogisticsCompaniesName(resultSet.getString("logistics_companies_name"));
            logisticsCompanies.setLogisticsCompaniesPhone(resultSet.getString("logistics_companies_phone"));
            orders.setLogisticsCompanies(logisticsCompanies);
            LogisticsStation logisticsStation = new LogisticsStation();
            logisticsStation.setLogisticsStationName(resultSet.getString("logistics_station_name"));
            logisticsStation.setLogisticsStationPhone(resultSet.getString("logistics_station_phone"));
            logisticsStation.setLogisticsStationLongitude(resultSet.getString("logistics_station_longitude"));
            logisticsStation.setLogisticsStationLatitude(resultSet.getString("logistics_station_latitude"));
            logisticsStation.setLogisticsStationDeliveryman(resultSet.getString("logistics_station_deliveryman"));
            logisticsStation.setLogisticsStationProvince(resultSet.getString("logistics_station_province"));
            logisticsStation.setLogisticsStationCity(resultSet.getString("logistics_station_city"));
            logisticsStation.setLogisticsStationDistrict(resultSet.getString("logistics_station_district"));
            orders.setLogisticsStation(logisticsStation);
            Coupons coupons = new Coupons();
            coupons.setCouponTitle("coupon_title");
            Warehouse warehouse=new Warehouse();
            warehouse.setWarehouseName(resultSet.getString("warehouse_name"));
            warehouse.setWarehouseLongitude(resultSet.getString("warehouse_longitude"));
            warehouse.setWarehouseLatitude(resultSet.getString("warehouse_latitude"));
            orders.setWarehouse(warehouse);
            ordersList.add(orders);
        }
        close(resultSet);
        close(specificationResultSet);
        close(insuranceResultSet);
        return ordersList;
    }


    /**
     * 根据账户ID,订单状态查询订单数量
     *
     * @param informationId 用户ID
     * @param orderState    订单状态
     * @return 订单数量
     * @throws SqlErrorException SQL执行异常
     */
    public int getOrderCount(int informationId, int orderState) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT COUNT(*) FROM orders o" +
                " join address a on a.address_id=o.address_id" +
                " WHERE information_id = ? and orders_status=?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, informationId, orderState);
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
        return 0;

    }


    /**
     * 根据账户ID,订单号删除订单
     *
     * @param informationId 用户ID
     * @param ordersId      订单号
     * @return true/false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean getOrderDelete(int informationId, String ordersId) throws SqlErrorException {
        @Language("sql")
        String sql = "delete o from address a" +
                "        inner join orders o on a.address_id = o.address_id" +
                "        where a.information_id=? and o.orders_id=?";
        Object[] params = {informationId, ordersId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }

    /**
     * 查询是否有被选中的地址
     * @param informationId 用户信息Id
     * @return true 有 false 无
     * @throws SqlErrorException 抛出错误
     */
    public boolean checkUnselectedAddress(int informationId) throws SqlErrorException {
        @Language("SQL")
        String sql = "SELECT COUNT(*) FROM address WHERE information_id=? AND is_selected_address=0";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, informationId);
            if (resultSet.next()) {
                return resultSet.getInt(1) > 0;
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
        return false;
    }
}
